package com.soda.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;





import com.soda.bean.News;
import com.soda.bean.Notice;
import com.soda.dao.BaseDao;
import com.soda.dao.NoticeDao;
import com.soda.util.PageInfo;
import com.soda.util.SelectNewsInfo;
import com.soda.util.SelectNoticeInfo;

public class NoticeDaoImpl extends BaseDao implements NoticeDao {
	 
	public int addNotice(Notice n) {
		try {
			getConnection();
			String sql = "INSERT INTO notice VALUES(default,?,?,?,?,NOW(),1)";
			pt = conn.prepareStatement(sql);
			pt.setString(1, n.getNotTitle());
			pt.setString(2, n.getNotUser());
			pt.setInt(3, n.getNotLevel());
			pt.setString(4, n.getNotContent());

			return pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	// 全查
	public Notice showAllNotice(int id) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			String sql = "select * from notice where not_id=?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, id);
			res = pt.executeQuery();
			if (res.next()) {
				Notice n = new Notice(res.getInt(1), res.getString(2),
						res.getString(3), res.getInt(4),
						 res.getString(5), res.getString(6), res.getInt(7));

				return n;

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return null;
	}

	// 条件查询
 
	public List<Notice> getNotices(SelectNoticeInfo sni, PageInfo pi) {
		
	 {
		// , PageInfo pi
		
			try {
				getConnection();
				StringBuffer sql = new StringBuffer(
						"select * from notice where not_delete=1");
				if (sni != null) {
					if (sni.getNotTitle() != null
							|| sni.getNotTitle().length() != 0) {
						sql.append(" and not_title like ?");
					}
					if (sni.getNotUpdateTime() != null
							|| sni.getNotUpdateTime().length() != 0) {
						sql.append(" and not_updatetime like ?");
					}

					if (sni.getNotLevel() == 1 || sni.getNotLevel() == 2|| sni.getNotLevel() == 3) {
						sql.append(" and not_level=?");
					}

				}
				sql.append(" order by not_id desc limit ?,? ");
				pt = conn.prepareStatement(sql.toString());
				int x = 1;
				if (sni != null) {
					if (sni.getNotTitle() != null
							|| sni.getNotTitle().length() != 0) {
						pt.setString(x, sni.getNotTitle() + "%");
						x++;
					}
					if (sni.getNotUpdateTime() != null
							|| sni.getNotUpdateTime().length() != 0) {
						pt.setString(x, sni.getNotUpdateTime() + "%");
						x++;
					}
					if (sni.getNotLevel() >= 0) {
						if (sni.getNotLevel() == 1 || sni.getNotLevel() ==2|| sni.getNotLevel() ==3) {
							pt.setInt(x, sni.getNotLevel());
							x++;
						}
					}
				}
				// 分页信息
				pt.setInt(x, (pi.getPage() - 1) * pi.getPer());
				x++;
				pt.setInt(x, pi.getPer());
				List<Notice> list = new ArrayList<Notice>();
				res = pt.executeQuery();
				while (res.next()) {
					Notice n = new Notice();
					n.setNotId(res.getInt(1));
					n.setNotTitle(res.getString(2));
					n.setNotUser(res.getString(3));
					n.setNotLevel(res.getInt(4));
					n.setNotContent(res.getString(5));
					n.setNotUpdateTime(res.getString(6));
					n.setNotDelete(res.getInt(7));
					list.add(n);
				}
				return list;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally{
				close();
			}return null;
	}
	
	}

	public int getNoticeCount(SelectNoticeInfo sni) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			StringBuffer sql = new StringBuffer(
					"select count(1) from notice where not_delete=1");
			if (sni != null) {
				if (sni.getNotTitle() != null
						|| sni.getNotTitle().length() != 0) {
					sql.append(" and not_title like ?");
				}
				if (sni.getNotUpdateTime() != null
						|| sni.getNotUpdateTime().length() != 0) {
					sql.append(" and not_updatetime like ?");
				}

				if (sni.getNotLevel()!=0) {
					sql.append(" and not_level=?");
				}

			}
			pt = conn.prepareStatement(sql.toString());
			int x = 1;
			if (sni != null) {
				if (sni.getNotTitle() != null
						|| sni.getNotTitle().length() != 0) {
					pt.setString(x, sni.getNotTitle() + "%");
					x++;
				}
				if (sni.getNotUpdateTime() != null
						|| sni.getNotUpdateTime().length() != 0) {
					pt.setString(x, sni.getNotUpdateTime() + "%");
					x++;
				}
				if (sni.getNotLevel() >= 0) {
					if (sni.getNotLevel() != 0 ) {
						pt.setInt(x, sni.getNotLevel());
						x++;
					}
				}
			}
			res = pt.executeQuery();
			if (res.next()) {
				return res.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	public List<Notice> selectNotice(PageInfo pi) {
		try {
			getConnection();
			String sql = "select * from notice limit ?,?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, (pi.getPage() - 1) * pi.getPer());
			pt.setInt(2, pi.getPer());
			res = pt.executeQuery();
			List<Notice> list = new ArrayList<Notice>();
			while (res.next()) {
				Notice n = new Notice(res.getInt(1), res.getString(2),
						res.getString(3), res.getInt(4), res.getString(5),
						res.getString(6),  res.getInt(7));
				list.add(n);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	// 单删
	public int deleteById(int id) {

		try {
			getConnection();
			String sql = "UPDATE notice SET not_delete=0 WHERE not_id=?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, id);
			return pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();

		}
		return 0;
	}

	// 批量删除
	public int delAllNotice(String[] nids) {
		try {
			getConnection();
			StringBuffer sql = new StringBuffer(
					"update notice set not_delete=0 where not_id in(");
			for (String i : nids) {
				sql.append(i + ",");
			}
			sql.deleteCharAt(sql.length() - 1);
			sql.append(")");

			pt = conn.prepareStatement(sql.toString());
			return pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return 0;
	}
	
	//导出
	public List<Notice>expolExcel(String[] nids) {
		try {
			getConnection();
			StringBuffer sql = new StringBuffer(
					"select not_id,not_title,not_updatetime,not_level from notice where not_id in(");
			for (String i : nids) {
				sql.append(i + ",");
			}
			sql.deleteCharAt(sql.length() - 1);
			sql.append(")");

			pt = conn.prepareStatement(sql.toString());
			List<Notice>list=new ArrayList<Notice>();
			res=pt.executeQuery();
			while(res.next()){
				Notice n=new Notice();
				n.setNotId(res.getInt(1));
				n.setNotTitle(res.getString(2));
				n.setNotUpdateTime(res.getString(3));
				n.setNotLevel(res.getInt(4));
				list.add(n);
			}
		return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return null;
	}
	
	
	// 修改

	
	public int updateNotice(int id, int recommend, String neirong) {

		try {
			getConnection();
			String sql = "UPDATE notice SET not_level=?,not_content=?,not_updatetime=NOW() WHERE not_id=?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, recommend);
			pt.setString(2, neirong);
			pt.setInt(3, id);
			return pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return 0;
	}

	public List<Notice> getSelectAll() {
		// TODO Auto-generated method stub
		try {
			getConnection();
			String sql = "SELECT * FROM notice limit 0,6";
			pt = conn.prepareStatement(sql);
			res = pt.executeQuery();
			List<Notice> list = new ArrayList<Notice>();
			while(res.next()){
				Notice nc = new Notice(res.getInt(1), res.getString(2), res.getString(3),res.getInt(4), res.getString(5), res.getString(6), res.getInt(7));
				
			    
				list.add(nc);
			}
			return list;
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			close();
			
		}
		return null;
	}
//导出
	public List<Notice> exportNotice(String[] count) {
		try{
		getConnection();
		StringBuffer sql=new StringBuffer("select * from notice where not_id in(");
		for (int i = 0; i < count.length; i++) {
			sql.append("?,");
		}
		sql.deleteCharAt(sql.length()-1);
		sql.append(")");
		pt=conn.prepareStatement(sql.toString());
		int x=1;
		for (int i = 0; i < count.length; i++) {
			pt.setInt(x, Integer.parseInt(count[i]));
			x++;
		}
		res=pt.executeQuery();
		List<Notice> list=new ArrayList<Notice>();
		while(res.next()){
			Notice notice=new Notice(res.getInt(1), res.getString(2),res.getString(3),  res.getInt(4), res.getString(5), res.getString(6),res.getInt(7));
			
			list.add(notice);
			
		}
		return list;
	} catch (NumberFormatException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		close();
	}	
		return null;

	}
 

}

